/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistence;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import servidorped.User;

/**
 *
 * @author Micosil
 */
public class ConversationDatabase implements ConversationPersistence {

    String url = null;
    String dbName = null;
    String driver = null;
    String databaseUserName = null;
    String databasePassword = null;

    public ConversationDatabase(String pathToConfigFile) {
        BufferedReader configReader = null;
        try {
            configReader = new BufferedReader(new FileReader(new File(pathToConfigFile)));
            configReader.readLine();
            url = configReader.readLine();
            dbName = configReader.readLine();
            driver = configReader.readLine();
            databaseUserName = configReader.readLine();
            databasePassword = configReader.readLine();

            //Connection test.
            Connection conn = openConnection();
            closeConnection(conn);

        } catch (IOException ex) {
            Logger.getGlobal().log(Level.SEVERE, "Could not load Database Parameters.");
        } finally {

            if (configReader != null) {
                try {
                    configReader.close();
                } catch (IOException ex) {
                    Logger.getGlobal().log(Level.SEVERE, null, ex);
                }
            }

        }

    }

    private Connection openConnection() {
        Connection conn = null;
        try {
            Class.forName(driver).newInstance();

            conn = DriverManager.getConnection(url + dbName, databaseUserName, databasePassword);
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, "Encountered the following exception when creating a connection: ", ex);
        }
        return conn;
    }

    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
            }
        }
    }

    private void closeResultSet(ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException ex) {
            }
        }
    }

    private void closePreparedStatement(PreparedStatement prep) {
        if (prep != null) {
            try {
                prep.close();
            } catch (SQLException ex) {
            }
        }
    }
    
    
    private String getDateAsString(Calendar cur) {
        return Integer.toString(cur.get(Calendar.YEAR)) + "-" + Integer.toString(cur.get(Calendar.MONTH)) + "-" + Integer.toString(cur.get(Calendar.DAY_OF_MONTH)) + "_" + Integer.toString(cur.get(Calendar.HOUR_OF_DAY)) + "-" + Integer.toString(cur.get(Calendar.MINUTE)) + "-" + Integer.toString(cur.get(Calendar.SECOND));
    }
    

    @Override
    public void storeConversation(List<User> usersInConvo, List<String> conversation, Date conversationStart) {
        Connection conn = openConnection();
        String fullconvo = "";
        Calendar cal = Calendar.getInstance();
        cal.setTime(conversationStart);
        for(String partialLine: conversation){
            fullconvo += partialLine + "\n";
        }
                
        PreparedStatement statement = null;        
        try {
            statement = conn.prepareStatement("INSERT INTO CHATMICOSIL.CONVERSATION (CONVERSATION_DATE, CONVERSATION_CONTENT) "
                    + "VALUES (?, ?)");
            statement.setString(1, getDateAsString(cal));
            statement.setString(2, fullconvo);
            statement.execute();
            
            closePreparedStatement(statement);            
            
            statement = conn.prepareStatement("INSERT INTO CHATMICOSIL.USUARIO_CONVERSATION (USUARIO_USERNAME, CONVERSATION_DATE) "
                    + "VALUES (?, ?)");
            statement.setString(2, getDateAsString(cal));
            for(User user: usersInConvo){
                statement.setString(1, user.getUserName());
                statement.execute();
            }
            
        } catch (SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, null, ex);            
        } finally {
            closeConnection(conn);
            closePreparedStatement(statement);            
        }        
    }

    @Override
    public List<String> findConversationsByUser(String userName) {
        Connection conn = openConnection();
        List<String> response = new ArrayList();
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            statement = conn.prepareStatement("SELECT CONVERSATION_DATE FROM CHATMICOSIL.USUARIO_CONVERSATION WHERE USUARIO_USERNAME = ?");
            statement.setString(1, userName);
            result = statement.executeQuery();
            while (result.next()) {
                response.add(result.getString("CONVERSATION_DATE"));
            }

        } catch (SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(conn);
            closePreparedStatement(statement);
            closeResultSet(result);
        }
        return response;
    }

    @Override
    public boolean isUserInConversation(String conversationDate, String userName) {

        Connection conn = openConnection();
        boolean response = false;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            statement = conn.prepareStatement("SELECT USUARIO_USERNAME FROM CHATMICOSIL.USUARIO_CONVERSATION WHERE CONVERSATION_DATE = ?");
            statement.setString(1, conversationDate);
            result = statement.executeQuery();
            while (result.next()) {
                if (result.getString("USUARIO_USERNAME").equals(userName)) {
                    response = true;
                    break;
                }
            }

        } catch (SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(conn);
            closePreparedStatement(statement);
            closeResultSet(result);
        }
        return response;

    }

    @Override
    public List<String> retrieveConversation(String conversationDate) {
        Connection conn = openConnection();
        List<String> response = new ArrayList();
        
        PreparedStatement statement = null;
        ResultSet result = null;        
        try {
            statement = conn.prepareStatement("SELECT CONVERSATION_CONTENT FROM CHATMICOSIL.CONVERSATION WHERE CONVERSATION_DATE = ?");
            statement.setString(1, conversationDate);
            result = statement.executeQuery();
            result.next();
            String fullChat = result.getString("CONVERSATION_CONTENT");
            String[] stringArray = fullChat.split("\n");
            for(String oneString:stringArray){
                response.add(oneString);
            }
        } catch (SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(conn);
            closePreparedStatement(statement);
            closeResultSet(result);
        }
        return response;
    }

    @Override
    public boolean conversationExists(String conversationDate) {
        Connection conn = openConnection();
        boolean response = false;
        
        PreparedStatement statement = null;
        ResultSet result = null;        
        try {
            statement = conn.prepareStatement("SELECT * FROM CHATMICOSIL.CONVERSATION WHERE CONVERSATION_DATE = ?");
            statement.setString(1, conversationDate);
            result = statement.executeQuery();
            response = result.next();
            
        } catch (SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(conn);
            closePreparedStatement(statement);
            closeResultSet(result);
        }
        return response;
     
    }

    @Override
    public List<String> getAllConversations() {
        Connection conn = openConnection();
        List<String> response = new ArrayList();
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            statement = conn.prepareStatement("SELECT CONVERSATION_DATE FROM CHATMICOSIL.USUARIO_CONVERSATION");            
            result = statement.executeQuery();
            result.next();
            while (result.next()) {
                response.add(result.getString("CONVERSATION_DATE"));
            }

        } catch (SQLException ex) {
            Logger.getGlobal().log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(conn);
            closePreparedStatement(statement);
            closeResultSet(result);
        }
        return response;
    }
}
